﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace FamilyTree_v1._0
{
    class DatabaseHelper
    {
      //  private string connectionString;
        DataFamilyTreeDataContext datafimalyContext;
        public DatabaseHelper()
        {
            datafimalyContext = new DataFamilyTreeDataContext();
        }
        protected void Connection()
        {
            //connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["LINQConnectionString"].ToString();
        }
        public Boolean AddPerson(Person aPerson)//them 1 nguoi
        {
            datafimalyContext.Persons.InsertOnSubmit(aPerson);
            datafimalyContext.SubmitChanges();
            return true;
        }
        public Person SearchPersonByID( int aPerID)// tim nguoi theo id
        {
            var arrPerson = from p in datafimalyContext.Persons
                            where p.PersonID==aPerID
                            select p;
            if(arrPerson.Count()>0)
            return arrPerson.First();
            return null;
        }
        public Boolean UpdatePerson(Person aNewPerson)// sua mot nguoi
        {
            Person aPer = SearchPersonByID(aNewPerson.PersonID);
            if (aPer != null)
            {
                aPer.PersonID = aNewPerson.PersonID;
                aPer.FirstName = aNewPerson.FirstName;
                aPer.MiddleName = aNewPerson.MiddleName;
                aPer.LastName = aNewPerson.LastName;
                aPer.OtherName = aNewPerson.OtherName;
                aPer.Image = aNewPerson.Image;
                aPer.DateOfBirth = aNewPerson.DateOfBirth;
                aPer.PlaceOfBirth = aNewPerson.PlaceOfBirth;
                aPer.Sex = aNewPerson.Sex;
                aPer.IDofFather = aNewPerson.IDofFather;
                aPer.IDofMother = aNewPerson.IDofMother;
                aPer.DateOfDeath = aNewPerson.DateOfDeath;
                aPer.Religious = aNewPerson.Religious;
                aPer.Career = aNewPerson.Career;
                aPer.Notes = aNewPerson.Notes;
                aPer.Generation = aNewPerson.Generation;
                aPer.Order = aNewPerson.Order;
                aPer.IsAncestor = aNewPerson.IsAncestor;
                aPer.IsChieftains = aNewPerson.IsChieftains;
                datafimalyContext.SubmitChanges();
                return true;
            }
            return false;
        }
        public Boolean DeletePerson(int aPerID)// xoa mot nguoi xoa luon cac moi lien ket toi node do
        {
            Person aPer = SearchPersonByID(aPerID);
            var assl = from a in datafimalyContext.Associates// lay node trai
                      where a.LeftID == aPer.PersonID 
                      select a;
            List<Associate> arrAssl = assl.ToList();
            foreach (Associate item in arrAssl)
            {
                UpdateAssociate(new Associate {AssociateID=item.AssociateID, LeftID = null });
            }
            var assr = from a in datafimalyContext.Associates// lay node phai
                      where a.RightID == aPer.PersonID
                      select a;
            List<Associate> arrAssr = assr.ToList();
            foreach (Associate item in arrAssr)
            {
                UpdateAssociate(new Associate { AssociateID = item.AssociateID, RightID = null });
            }
            datafimalyContext.Persons.DeleteOnSubmit(aPer);
            datafimalyContext.SubmitChanges();
            return true;
        }
        public Boolean AddAssociate(Associate aAssociate)
        {
            datafimalyContext.Associates.InsertOnSubmit(aAssociate);
            datafimalyContext.SubmitChanges();
            return true;
        }
        public Associate SearchAssociateByID(int aAssociateID)
        {
            var arrAssociate = from p in datafimalyContext.Associates
                               where p.AssociateID == aAssociateID
                            select p;
            return arrAssociate.First();
        }
        public Associate SearchAssociateByPersonID(int aPersonID)
        {
            var arrAssociate = from p in datafimalyContext.Associates
                               where p.PersonID == aPersonID
                               select p;
            return arrAssociate.First();
        }
        public Boolean UpdateAssociate(Associate aNewAssociate)
        {
            Associate aAss = SearchAssociateByID(aNewAssociate.AssociateID);
            aAss.PersonID = aNewAssociate.PersonID;
            aAss.LeftID = aNewAssociate.LeftID;
            aAss.RightID = aNewAssociate.RightID;
            datafimalyContext.SubmitChanges();
            return true;
        }
        public Boolean DeleteAssociate(int aAssID)
        {
            Associate aAss = SearchAssociateByID(aAssID);
            datafimalyContext.Associates.DeleteOnSubmit(aAss);
            datafimalyContext.SubmitChanges();
            return true;
        }
        public Boolean AddMarriage(Marriage aMarriage)
        {
            datafimalyContext.Marriages.InsertOnSubmit(aMarriage);
            datafimalyContext.SubmitChanges();
            return true;
        }
        public Marriage SearchMarriageByID(int aMarID)
        {
            var arrMarriage = from p in datafimalyContext.Marriages
                            where p.MarriageID == aMarID
                            select p;
            if(arrMarriage.Count()>0)
                return arrMarriage.First();
            return null;
        }
       
        public Boolean UpdateMarriage(Marriage aNewMarriage)
        {
            Marriage aMar = SearchMarriageByID(aNewMarriage.MarriageID);
            aMar.MaleID = aNewMarriage.MaleID;
            aMar.FemaleID = aNewMarriage.FemaleID;
            datafimalyContext.SubmitChanges();
            return true;
        }
        public Boolean DeleteMarriage(int aMarID)
        {
            Marriage aMar = SearchMarriageByID(aMarID);
            datafimalyContext.Marriages.DeleteOnSubmit(aMar);
            datafimalyContext.SubmitChanges();
            return true;
        }
        public Boolean AddChid(int aFatherID,Person aFirstChid)// add chid
        {
            AddPerson(aFirstChid);
            Associate ass = SearchAssociateByPersonID(aFatherID);
            if (ass != null)
            {
                UpdateAssociate(new Associate { AssociateID = ass.AssociateID, LeftID = aFirstChid.PersonID });
            }
            else
            {
                ass = new Associate();
                ass.PersonID = aFatherID;
                ass.LeftID = aFirstChid.PersonID;
                AddAssociate(ass);
            }
            return true;
        }
        public Boolean AddSibling(int aPersonID, Person aBrother)// add anh em
        {
            AddPerson(aBrother);
            Associate ass = SearchAssociateByPersonID(aPersonID);
            if (ass != null)
            {
                UpdateAssociate(new Associate { AssociateID = ass.AssociateID, RightID = aBrother.PersonID });
            }
            else
            {
                ass = new Associate();
                ass.PersonID = aPersonID;
                ass.LeftID = aBrother.PersonID;
                AddAssociate(ass);
            }
            return true;
        }
        public Boolean AddMarriage(int aHusID,int aWifeID)
        {
            Marriage aMar = new Marriage();
            aMar.MaleID = aHusID;
            aMar.FemaleID = aWifeID;
            AddMarriage(aMar);
            return true;
        }
    }
}
